
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE NAME='sp_SearchSinhVien')
BEGIN
	DROP PROC sp_SearchSinhVien
END
GO
CREATE PROC sp_SearchSinhVien
	@HOTEN		NVARCHAR(40),
	@MA_LOP INT,
	
AS
BEGIN
	IF (@HOTEN = '' AND @MA_LOP = '') -- TH1 SEARCH ALL
	BEGIN
		SELECT * FROM SINHVIEN
	END
	
	IF (@HOTEN <> '' AND @MA_LOP = '') -- TH2 SEARCH HOTEN
	BEGIN
		SELECT * FROM SINHVIEN WHERE HOTEN like '%' + @HOTEN + '%'
	END

	IF (@HOTEN = '' AND @MA_LOP <> '') -- TH3 SEARCH MA_LOP
	BEGIN
		SELECT * FROM SINHVIEN WHERE MA_LOP like '%' + @MA_LOP + '%'
	END

	IF (@HOTEN <> '' AND @MA_LOP <> '') -- TH4 SEARCH HOTEN VA MA_LOP
	BEGIN
		SELECT * FROM SINHVIEN WHERE MA_LOP like '%' + @MA_LOP + '%' AND HOTEN like '%' + @HOTEN + '%'
	END

END